package com.v8dn.app.util.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @title Excel写操作帮助类
 * @author lhf
 * @date 2012-10-30 下午11:08:21
 * @description 兼容03和07版本的excel
 */
public class POIExcelUtil{
	private static Log log=LogFactory.getLog(POIExcelUtil.class);
	
	/**
	 * @title 将Workbook写入Excel文件
	 * @param wb Workbook
	 * @param fileName 写入文件的相对路径
	 */
	public static void writeWorkbook(Workbook wb,String fileName){
		FileOutputStream fos=null;
		try{
			fos=new FileOutputStream(fileName);
			wb.write(fos);
		}catch(FileNotFoundException e){
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		}catch(IOException e){
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		}finally{
			try{
				if(fos!=null){
					fos.close();
				}
			}catch(IOException e){
				log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
			}
		}
	}

	/**
	 * @title 创建Sheet工作簿
	 * @param wb Workbook
	 * @param sheetName String
	 * @return Sheet
	 */
	public static Sheet createSheet(Workbook wb,String sheetName){
		Sheet sheet=wb.createSheet(sheetName);
		sheet.setDefaultColumnWidth(12);
		sheet.setDisplayGridlines(false);
		return sheet;
	}

	/**
	 * @title 创建Row
	 * @param sheet Sheet
	 * @param rowNum int
	 * @param height int
	 * @return Row
	 */
	public static Row createRow(Sheet sheet,int rowNum,int height){
		Row row=sheet.createRow(rowNum);
		row.setHeight((short)height);
		return row;
	}

	/**
	 * @title 创建CellStyle样式
	 * @param wb Workbook
	 * @param backgroundColor 背景色
	 * @param foregroundColor 前置色
	 * @param font 字体
	 * @return CellStyle
	 */
	public static CellStyle createCellStyle(Workbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
		CellStyle cs=wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(font);
		return cs;
	}

	/**
	 * @title 创建带边框的CellStyle样式
	 * @param wb Workbook
	 * @param backgroundColor 背景色
	 * @param foregroundColor 前置色
	 * @param font 字体
	 * @return CellStyle
	 */
	public static CellStyle createBorderCellStyle(Workbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
		CellStyle cs=wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(font);
		cs.setBorderLeft(CellStyle.BORDER_DASHED);
		cs.setBorderRight(CellStyle.BORDER_DASHED);
		cs.setBorderTop(CellStyle.BORDER_DASHED);
		cs.setBorderBottom(CellStyle.BORDER_DASHED);
		return cs;
	}

	/**
	 * @title 创建CELL
	 * @param row Row
	 * @param cellNum int
	 * @param style HSSFStyle
	 * @return Cell
	 */
	public static Cell createCell(Row row,int cellNum,CellStyle style){
		Cell cell=row.createCell(cellNum);
		cell.setCellStyle(style);
		return cell;
	}

	/**
	 * @title 合并单元格
	 * @param sheet Sheet
	 * @param firstRow int
	 * @param lastRow int
	 * @param firstColumn int
	 * @param lastColumn int
	 * @return int 合并区域号码
	 */
	public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){
		return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));
	}

	/**
	 * @title 创建字体
	 * @param wb Workbook
	 * @param boldweight short
	 * @param color short
	 * @return Font
	 */
	public static Font createFont(Workbook wb,short boldweight,short color,short size){
		Font font=wb.createFont();
		font.setBoldweight(boldweight);
		font.setColor(color);
		font.setFontHeightInPoints(size);
		return font;
	}

	/**
	 * @title 设置合并单元格的边框样式
	 * @param sheet Sheet
	 * @param ca CellRangAddress
	 * @param style CellStyle
	 */
	public static void setRegionStyle(Sheet sheet,CellRangeAddress ca,CellStyle style){
		for(int i=ca.getFirstRow();i<=ca.getLastRow();i++){
			Row row=CellUtil.getRow(i,sheet);
			for(int j=ca.getFirstColumn();j<=ca.getLastColumn();j++){
				Cell cell=CellUtil.getCell(row,j);
				cell.setCellStyle(style);
			}
		}
	}
}
